Deadlock while updating rows and inserting new ones I'm working with SQL Server 2012 Express Edition and I getting this error:
Transaction (Process ID 53) was deadlocked on lock resources with another process and has
been chosen as the deadlock victim. Rerun the transaction.

I've added these two commands to show more information about it:
DBCC TRACEON(1204, -1)
DBCC TRACEON(1222, -1)

With these two commands I get this log. But I don't know how to find where the problem is.
Executing sp_lock I have identified that the problem is on table TRZIC.dbo.CODES and it has a PAG type.
On this table I'm updating rows and inserting new ones at the same time.
Any advice? How can I solve this deadlock?

I bet your code does something like this logic:

IF EXISTS (SELECT 1 FROM dbo.table WHERE key = @key)
BEGIN
  UPDATE dbo.table SET foo = @bar WHERE key = @key;
END
ELSE
BEGIN
  INSERT dbo.table(key, foo) VALUES(@key, @bar);
END

If so, just do this:

UPDATE dbo.table SET foo = @bar WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(key, foo) VALUES(@key, @bar);
END

That meaningless first SELECT is just a wasted seek (or scan if you are not identifying row(s) by something that can seek), and can often be the cause of deadlocks when you have many users simultaneously running this same piece of code.


Determine the root cause of Failover Is there anyway to know when was the last fail-over happen and exact Root cause of fail-over? Can we get history of all the previous primary nodes list and time of their fail-overs?
I have explored a lot in internet. I didn't get the exact steps to see that.

Is there anyway to know when was the last fail-over happen

Yes, have alerts and monitoring setup to log this when it happens.

  and exact Root cause of fail-over?

Sure, you log it manually when you figure out what the root cause is. There is nothing automatic that will magically give you a root cause for the failure - that's your job.

  Can we get history of all the previous primary nodes list and time of their fail-overs?

Unless you already have this information logged somewhere, no there is no place that this is automatically logged at.

You can attempt to slog through the engine errorlog, window event logs, and cluster logs... but there is nothing to say that they haven't rolled over or have been otherwise cleared.

  I have explored a lot in internet. I didn't get the exact steps to see that.

Because nothing that's automatic currently exists inside the product.


Postgres text column too large for an index - searching for IP address, so I don't think text search will work I have a Postgres database with a column that has a large amount of text in it.  There are potentially multiple IP addresses in the text, as well as a lot of other text.

I need to do a query

WHERE value LIKE '%' || ipValue || '%'

which would be like:

WHERE value LIKE '%192.168.2.1%'

The column is too big for an index:

  [54000] ERROR: index row size 2872 exceeds btree version 4 maximum
  2704 for index "rtfdata_value_index" Detail: Index row references
  tuple (34153,51) in relation "rtfdata". Hint: Values larger than 1/3
  of a buffer page cannot be indexed. Consider a function index of an
  MD5 hash of the value, or use full text indexing.

An MD5 hash of the column value clearly will not work, and I don't think that a full-text index will allow me to search on the IP (since it's not a language word).

What can I do here?

My current best idea is to write a script that will create yet another table with a record_id and an ip_address column that will search each text column with a regex and pull out all the IPs and create 1 row per IP/record in this table.  Then I can join this table when looking for the IP, instead of the wildcard search on the large text column.

You need a function which extracts the IP addresses into an array.  The regexp you gave doesn't work for me, so I came up with my own crude one, you will probably want to tweak it to suit yourself:

create or replace function extract_ip(text) returns text[] immutable language SQL as $$
  select array_agg(x[1]) from regexp_matches($1,'\d+.\d+.\d+.\d+','g')f(x)
 $$;
create index on foobar using gin (extract_ip(value));
select * from foobar where extract_ip(value) && ARRAY['192.168.2.1'];


PostgreSQL How to DEFAULT Partitioned Identity Column? PostgreSQL 11
What is the best way to generate default values for identity columns on partition tables.
E.g

CREATE TABLE data.log
(
  id              BIGINT GENERATED ALWAYS AS IDENTITY
                  (
                    INCREMENT BY 1
                    MINVALUE -9223372036854775808
                    MAXVALUE 9223372036854775807
                    START WITH -9223372036854775808
                    RESTART WITH -9223372036854775808
                    CYCLE
                  ),
  epoch_millis    BIGINT NOT NULL,
  message         TEXT NOT NULL

) PARTITION BY RANGE (epoch_millis);

CREATE TABLE data.foo_log
PARTITION OF data.log
(
  PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);

If I do:

INSERT INTO data.foo_log (epoch_millis, message)
VALUES (1000000, 'hello');

I get:

  ERROR: null value in column "id" violates not-null constraint
  DETAIL: Failing row contains (null, 1000000, hello).
  SQL state: 23502

because the default generated value is not applied to the partition UNLESS I insert it into the root table like this:

INSERT INTO data.log (epoch_millis, message)
VALUES (1000000, 'hello');

There are times though that I want to insert directly into a specific partition for performance reasons (like doing bulk COPY).
The only way I can get this to work is to create the partition while knowing about the sequence that was implicitly created for the identity column like this:

CREATE TABLE data.foo_log
PARTITION OF data.log
(
  id DEFAULT nextval('data.log_id_seq'),
  PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);

Is there a better way to do this and if so how?

I don't know of a better solution in general. A few minor things, though:

pg_get_serial_sequence()

If you don't know the name of the parent's implicit sequence, use pg_get_serial_sequence().

SELECT pg_get_serial_sequence('data.log', 'id');

You might even use the expression in the CREATE TABLE script directly, but that would impose a very minor additional cost to compute the actual name for the default (once per transaction, I think), and since this is about performance optimization ...

COPY overrides GENERATED ALWAYS, but not trigger

Defining your id column as GENERATED ALWAYS AS IDENTITY has the effect that you are never allowed to provide user values for the column id in INSERT statements, even when using the override clause like:

INSERT INTO data.log (epoch_millis, message) OVERRIDING USER VALUE
VALUES (1000000, 'hello');

It would have to be GENERATED BY DEFAULT for this to work, or omit id from the INSERT completely. The manual:

  OVERRIDING USER VALUE

  If this clause is specified, then any values supplied for identity
  columns defined as GENERATED BY DEFAULT are ignored and the default
  sequence-generated values are applied.

  This clause is useful for example when copying values between tables.
  Writing INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 will
  copy from tbl1 all columns that are not identity columns in tbl2 while
  values for the identity columns in tbl2 will be generated by the
  sequences associated with tbl2.

COPY still overrides in any case. The manual:

  For identity columns, the COPY FROM command will always write the
  column values provided in the input data, like the INSERT option
  OVERRIDING SYSTEM VALUE.

But while writing to a partition directly, with your solution, INSERT also  overrides, so it will be your responsibility to avoid providing user values for the id column directly. An alternative would be to use a trigger instead of the default value in the partition:

CREATE OR REPLACE FUNCTION trg_log_default_id()
  RETURNS trigger AS
$func$
BEGIN
   NEW.id := nextval('data.log_id_seq')
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insbef_default_id
  BEFORE INSERT ON data.foo_log  -- the partition
  FOR EACH ROW
  EXECUTE PROCEDURE trg_log_default_id();

This assigns a number from the sequence in any case, more closely emulating the GENERATED ALWAYS behavior of the parent - stricter, even, also preventing COPY from violating your rule. The manual:

  COPY FROM will invoke any triggers and check constraints on the destination table.

But the trigger is a bit more expensive than a plain default value. And it would burn an extra serial number per row for regular inserts via the parent table. (It should be possible to distinguish cases in the trigger, didn't try now.)


Reducing Key Lookups I am using SQL server, and I have been looking closely at the concept of key lookups,

http://blog.sqlauthority.com/2009/10/07/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-remove-key-lookup/

So if you have a key lookup you can create an index with the 'include' columns to cover the non index columns you have in the select statement.

For instance,

SELECT ID, FirstName FROM OneIndex WHERE City = 'Las Vegas'
GO

This index will include a key lookup,

CREATE NONCLUSTERED INDEX [IX_OneIndex_City] ON [dbo].[OneIndex]
(
[City] ASC
) ON [PRIMARY]
GO

But this one will remove the key lookup,

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO

I mean how much of an impact will this have on performance? The key lookup has an operator cost of 0.295969 (99%), but what does that really mean?

How do you know that you need the second index there, and at what point does it become the case that you are trying to add too many indexes and it is not worth it?

It seems to me that some queries can include index scans, key lookups, and still seem to perform very fast.

Background
In the worst case, a query containing a lookup has to go to physical storage for rows that require column data not covered by the nonclustered index.  In the very worst of worst cases, each lookup will require a separate I/O, and execution will have to wait for that single row's worth of data to come back before proceeding.  This scenario usually has severe performance implications if the lookup has to process a significant number of rows.
That is why lookups get such a bad press.  On the other hand, consider that the ability to do lookups was introduced in SQL Server 2000.  In SQL Server 7.0 the query processor could only use a nonclustered index if it contained all the information necessary to satisfy the query; in all other cases, it had to access data via a clustered index (if present, or a heap scan otherwise).  If lookups were always so very bad, SQL Server would surely never have introduced them.
In SQL Server 2000+ then, where we have a nonclustered index that provides useful ordering and/or (most of) the columns required by a query, and where the number of lookups is likely to be relatively small, using the nonclustered index and performing a limited number of lookups on the base table is likely to be the cheapest available access method (though a fully-covering nonclustered index might be cheaper still, of course).
In many cases, it is just not practical to create as many nonclustered indexes as would be needed to avoid scanning the base table for all common queries.  One reason might be that INSERT/UPDATE/DELETE/MERGE performance is more important that querying speed (remember that data modification operations also have to maintain all affected nonclustered indexes).  Another reason might be space; each nonclustered index represents a copy of a subset of the columns of the base table (or expressions thereon) just sorted differently.  More copies of the data means more storage space, and more things competing for space in SQL Server's in-memory data cache.
Other times, we can create just a few extra indexes (perhaps filtered in SQL Server 2008+) with just enough INCLUDE columns to satisfy the vast majority of performance-critical queries, without compromising data modification performance too much, and without using too much extra disk space.  Balancing the competing considerations is what makes index tuning more art than science.
Cost
You ask what the 99% cost for the lookup operator really means in the query plan.  The costing component of the query optimizer produces an estimated cost for that operation that is 99% of the total estimated for the query.  The number itself (0.29) does not mean very much at all; for all practical purposes, you should consider it as a unit-less number used internally by the optimizer when comparing alternative strategies for that specific query.
The estimated cost takes no account of your hardware, configuration, application needs, or very much of anything else.  The cost model used by the optimizer includes a significant number of heuristics and simplifying assumptions that happen to produce reasonable plans most of the time, for most queries, on most hardware.  That's not to say that there is no correlation between high-cost operators in plans and performance; rather the link is often much weaker than commonly expected.  By all means check the reasons for high-estimated-cost plan operators first, but don't treat the information as anything other than a very possibly flawed estimate.
Impact
I also want to mention a couple of factors that can ameliorate the impact of lookups.  First, I mentioned right at the start that the worst case involves row-by-row physical I/O.  This will obviously be avoided if the data pages (clustered index or heap) needed to satisfy the lookups are already in memory (data cache). Where this is the case, the execution time difference between a plan with a lookup versus a covering index may well be immeasurable.  Even where physical I/O is required, if the number of reads is small, you still may not care.  (How likely data pages for a table are to be in the data cache depends on many factors, and will be specific to your hardware and circumstances).
Where more that a little physical I/O is needed, the impact of the lookups may still be reduced by optimizations present in the query plan.  If SQL Server expects the number of lookups to be significant, it may choose to explicitly sort the rows entering the nested loops join driving the lookup in order of the non-clustered keys.  This reordering promotes sequential reading of the nonclustered index, which may be very much faster than random I/O on your hardware.
With or without an explicit sort, the nested loops join driving the lookup may have the WithOrderedPrefetch or WithUnorderedPrefetch attributes present.  In either case, the query execution engine 'looks ahead' in the index key stream driving the lookups and issues read-ahead reads.  The idea is to issue asynchronous read requests to the I/O system for data pages that will be needed soon, so that by the time the lookup needs a data page, it is already present in memory.
Under ideal conditions (low fragmentation, good query plan, high-performance I/O system) the read-ahead mechanism may well be fast enough to prevent even large parallel query plans from ever waiting on I/O to complete.  This is especially true in Enterprise Edition, which can issue very large single I/O requests (up to 2MB per request if memory serves).  On the other hand, under less than ideal (more normal!) conditions, your query may suffer horribly as it waits on long I/O queues, or fails to drive the I/O system hard enough.  The worst case performance of key lookups can be very poor indeed.
Summary
In summary, you will generally want to avoid lookups where it makes sense to do so.  For small queries (that are going to remain small) you may decide that the overhead of extra indexes (space & maintenance) is not justified, given due weight to the wider needs of the system and its users.
Ultimately this is all part of the art & science that is database development and administration.


Finding list of Duplicate records from 2 tables in DB2 I have a Person table and Address Table. Their relationship is Person.AddrId = Address.Id

Person table has First_name, Last_name, and AddrId.

The Address table has Address1, Zipcode, and Id.

I want to get all the list of duplicate records from this columns combination.

I tried below query:

SELECT  A.FIRST_NAME, A.LAST_NAME, B.ADDR, B.ZIPCODE, count(1)
FROM  SCHEMA.PERSON A, SCHEMA.ADDRESS B
WHERE
A.ADDR_ID = B.ID
group by A.FIRST_NAME, A.LASTT_NAME, B.ADDR, B.ZIPCODE
having count(1) > 1

Unfortunately this gives only the duplicate records. I want both original as well as duplicate records.

SQL is closed so the result of a query is a new table that can be futher used. You can therefore join the result of your duplicates query with the original tables. Something like below should work:

select t2.*
from (
    SELECT  A.FIRST_NAME, A.LAST_NAME, B.ADDR, B.ZIPCODE
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
    group by A.FIRST_NAME, A.LASTT_NAME, B.ADDR, B.ZIPCODE
    having count(1) > 1
) as t1
join (
    SELECT  A.*, B.*
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
) as t2
     on (t1.FIRST_NAME, t1.LAST_NAME, t1.ADDR, t1.ZIPCODE)
      = (t2.FIRST_NAME, t2.LAST_NAME, t2.ADDR, t2.ZIPCODE)

Using a CTE will probably be a bit more efficient:

 with t as (
    SELECT  A.*, B.*
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
)
select t1.*
from t1
join (
    SELECT  FIRST_NAME, LAST_NAME, ADDR, ZIPCODE
    FROM  T1
    group by FIRST_NAME, LASTT_NAME, ADDR, ZIPCODE
    having count(1) > 1
) as t2
    on (t1.FIRST_NAME, t1.LAST_NAME, t1.ADDR, t1.ZIPCODE)
     = (t2.FIRST_NAME, t2.LAST_NAME, t2.ADDR, t2.ZIPCODE)

I replaced the "," joins with explicit ones, I also used * since I did not know the names of the additional columns.


SQL Server - How to restore the database except specific tables Can someone provide your insights, what is best option to restore the database from SQL Server (say prod to integration). I want to exclude specific tables during restore.

Using Script
Using SSIS
Using 3rd Party Tools.

If am using SSIS Integration Instance, does it involve licensing cost ? Some thoughts on this would help.

To exclude certain tables from your refresh of the integration environment, with a backup from production, you will need to make use of a staging\data cleansing environment.

The plan would be:

Backup production
Restore backup in staging
Drop the unwanted tables
Backup staging
Restore backup in integration

This plan could easily be implemented with PowerShell (DBATools is well worth a look) or SSIS.

If you like coding, go for PowerShell. If you like dragging icons around the screen, go for SSIS. Both can get the job done.


Can I store half of a record on one page and half of the record on another page? I'm watching Paul Randal's MCM video from http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx

From 27:11 of the video, the author said a record must be saved in one page, you can't have half of a record on one page and half of it on another page. This opinion is not what I learned. However I'm not confident about it, since I'm not an English native speaker and may misunderstood Paul's opinion. Anyone can help to explain a little?

Thanks in advance.

You are splitting hairs over semantics. A physical record cannot span pages. A physical record has to fit in a page.

But a index (or heap) row consists potentially from more than one physical records. An index row will consist from exactly one record in the IN_ROW_DATA allocation unit and zero, one or more records in ROW_OVEFLOW_DATA and/or LOB_DATA. The IN_ROW_DATA record contains pointers to overflow or LOB records. These off-row records themselves may contain more pointers to form a chain or a tree (this is how a VARCHAR(MAX) column can have  values that are obviously much larger than 8060 bytes). Such a large value will consist from many individual records, each of 8060 bytes or less. None of these record will ever span multiple pages.

And I did not even touch columnstore index rows, which are completely different.


Copy large database from one server to another I want to copy a database from one server to another. The source database is a geo-replicated read-only secondary. In case it matters, I want to use this as the source to avoid loading up the production (primary) database.
The source and target servers are in different resource groups, but they are located in the same area and in the same subscription.
This might seem like pretty standard stuff, but the problem I am having is that neither the export functionality nor the copy functionality within the Azure portal will work. The export fails with

Error code 400: the size of the database to be exported is bigger than what is supported for this operation.

The copy fails in a slightly different way; when I try to configure the target database I cannot set the data storage size, it defaults to 32GB and the slider to change it is missing:

The source database is a little over 1TB in size.
What other options do I have for creating a copy of that database? Or is there a way to work around the error displayed above?

The slide to increase the size should be there. If you don't see maybe your user isn't allowed to take such decision:

By the way, you said that your database is actually slightly more than 1TB.
In this case you have to move to Premium P11 or  P15.
Moving it there will presents some constraints.


What is the difference between these two create table statements? a)

SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);

Table created.

SQL> desc xml_tab1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PODOC                                              PUBLIC.XMLTYPE

b)

SQL> CREATE TABLE xml_tab2 of XMLType;

Table created.

SQL> desc xml_tab2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE of PUBLIC.XMLTYPE

Code from here.

In the first one, we are creating a column of type XMLTYPE. What does the second one do? When should I use the first or the second one?

The second statement is creating an object table.

It is almost never the case that you really want to use an object table.  That was something that was introduced in the 8i time frame when Oracle was making the database object oriented.  While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something that I'd recommend.


Compile Dynamic Sql before Running I have a table, and want to test if dynamic sql will compile.
Example:

create Table dbo.Product
(
     ProductId int,
     ProductName varchar(255)
)

This will succeed:  Column Name exists

declare @sqltext varchar(4000) = 'select ProductName from dbo.Product'
exec(@sqltext)

This will fail, Column Name  does not exist

declare @sqltext varchar(4000) = 'select ProductTestABCDName from dbo.Product'
exec(@sqltext)

How do I ensure dynamic sql will compile before running?

What I could do is create test stored procedure to compile, then drop the procedure afterwards, however seems inefficient. Is there a better way to compile code?

create procedure dbo.TestCompile as
drop procedure dbo.TestCompile

Is there like a sp_compilecode function in Sql server?

I found the information in this post about SQL Server function to check dynamic SQL syntax quite helpful.

The post author created a UDF that accepts a dynamic SQL statement and the UDF uses sys.dm_exec_describe_first_result_set (available in SQL Server 2012) to validate the SQL.

From that post:

-- =============================================
-- Author:  Eli Leiba
-- Create date: 06-2017
-- Description: Check Dynamic SQL Statement Syntax
-- =============================================
CREATE FUNCTION dbo.CheckDynaSQL (@p1 VARCHAR (2000))
RETURNS VARCHAR (1000)
AS
BEGIN
   DECLARE @Result VARCHAR (1000)

   IF EXISTS (
         SELECT 1
         FROM sys.dm_exec_describe_first_result_set (@p1, NULL, 0)
         WHERE [error_message] IS NOT NULL
            AND [error_number] IS NOT NULL
            AND [error_severity] IS NOT NULL
            AND [error_state] IS NOT NULL
            AND [error_type] IS NOT NULL
            AND [error_type_desc] IS NOT NULL
         )
   BEGIN
      SELECT @Result = [error_message]
      FROM sys.dm_exec_describe_first_result_set(@p1, NULL, 0)
      WHERE column_ordinal = 0
   END
   ELSE
   BEGIN
      SET @Result = 'OK'
   END

   RETURN (@Result)
END
GO

Declare @cmd varchar(1000)
set @cmd = 'select * from sys.databases'
Select dbo.CheckDynaSQL (@cmd)
---
OK

Declare @cmd varchar(1000)
set @cmd = 'select * from sys.databas'
Select dbo.CheckDynaSQL (@cmd)
---
Invalid object name 'sys.databas'.


Is it always faster to create indexes after loading data? I have a large number of records (~1 billion) that I need to load into MongoDB (actually TokuMX, but whatever). I have about 6 different indices I need to create on the collection. Is it always faster to load the data, and then create the indices? When I look at Mongo's logfile, It seems like Mongo does some kind of large operation (maybe a row count?) before actually starting index creation, and it does this for every index I create.

Will it always be faster to create the indices after loading the data?

If I wait until after loading the data, would it be faster to create each index in the background at the same time rather creating them than one-by-one?

Back in the day we would bulk load our data in this way:

Drop indexes
Load data in the order for which the clustered index would be built (i.e., you export the data in a precise way)
After the load is completed, create the clustered index
Next, create any additional non-clustered indexes
Miller time (this was before I could afford decent beer)

That method always proved faster than leaving the indexes in place. However, this was for Sybase and SQL Server. I imagine other systems would be similar, but I can't say for certain.


Table design - parent with always exactly 1 or 2 children Lets say I have the following existing tables (both with a primary key column named Id):
TableA
TableB

Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 records from TableB.  The relationship here is always either 1 or 2 records and will never change, but the order of these 2 relations does matter.  I'm calling this new table Groups but I can't decide on the proper way to design it.
Option 1 - flat
Groups
----------------------------
Id INT NOT NULL PRIMARY KEY,
TableAId INT NOT NULL,
TableBId1 INT NOT NULL,
TableBId2 INT NULL

Option 2 - details table
Groups                         GroupDetails
----------------------------   --------------
Id INT NOT NULL PRIMARY KEY,   GroupId INT NOT NULL,
TableAId INT NOT NULL          TableBId INT NOT NULL,
                               Sort TINYINT NOT NULL

Option 1 will make it easier to retrieve records since it reduces a join, but the column TableBId2 could potentially be null in some cases.
Option 2 will require a join, but it's more normalized and just seems like the "proper" way to design this table.
However, in this case I'm leaning towards breaking the normalization principles in favor of a slight optimization, because like I said, each Group record will always have at least 1 relation to TableB, and at most, a 2nd relation.  I'm struggling to make a decision on this, so I'm hoping someone can give their opinion on it.

The second option seems a bit odd. Why do you need the GroupDetails table instead of having a regular link table with 1 or 2 rows.

Groups
----------------------------
Id INT NOT NULL PRIMARY KEY
TableAId INT NOT NULL
TableBId INT NOT NULL
Sort TINYINT NOT NULL

In any case if you don't need to support more than two links, I would choose the first option. There's nothing wrong with having a null value in a column, so that's not an issue. You also get rid of the cumbersome sort column, which for 2 values is pretty much overkill anyway.


How to completely get rid of replication subscriptions? I have restored a database in another server. I don't want any of the transactional subscriptions of the former database.  I already called

exec sp_removedbreplication 'MyRestoredDB'
go

And also

exec sp_cleanupdbreplication
go

And

exec sp_replicationdboption 'MyRestoredDB','Publish','False',1
go

But when I try

truncate table dbo.sample

I get the following error:

  Msg 4711, Level 16, State 1, Line 7 Cannot truncate table 'sample'
  because it is published for replication or enabled for Change Data
  Capture.

I verified that the database isn't enabled for CDC.  What else I can do to perform the truncate?  Delete isn't an option for reasons not relevant to the question.

Take a look at this article: https://www.mssqltips.com/sqlservertip/2710/steps-to-clean-up-orphaned-replication-settings-in-sql-server/

Try dropping the old subscriptions, all publications and then removing replication from the DB, i.e.

exec sp_dropsubscription --on all subscriptions

then

exec sp_droppublication --on all publications

then

exec sp_replicationdboption

Also, take a look at this question: How to fix a messed up replication on MS SQL Server


Combining (joining) tables with no key I have table B and I have Table A.  Table B contains records with dates I need to pull. However, I only want to pull them if:

They do not exist at all in table A
They exist in table A, but only when table A.status = 0

The trick is I have no key to just join the tables with. So, I am using the Item column and the date column to join. (Notice two items can have the same date. An there can also be multiple of the same items, but with different dates as well.)

So table B might have:

    [Item]      [Qty]   [Date]
    F-001100,   400      7/14/2016
    F-002200,   750      7/28/2016
    F-000767,   1000     7/28/2016
    F-006006,   300      8/01/2016
    F-002200,   900      7/30/2016

Table A might have:

[Item]    [Status]  [Qty]    [Date0]     [Date2]     [Date3]     [Date4]
F-001100, 1,        400      7/14/2016   7/15/2016   7/16/2016   7/17/2016
F-002200, 0,        750      7/28/2016   7/29/2016   7/30/2016   7/31/2016

So the result set should be:

[Item]       [Qty]     [Date]
F-002200,    750       7/28/2016
F-000767,    1000      7/28/2016
F-006006,    300       8/01/2016
F-002200,    900       7/30/2016   <--- I can't get this to show up!!

The matching works like this:

WHERE A.[Item] = B.[Item]
AND (   A.[Date1] = B.[Date]
     OR A.[Date2] = B.[Date]
     OR A.[Date3] = B.[Date]
     OR A.[Date4] = B.[Date] )

It looks like your two conditions can be reduced to just one:

They do not exist in table A with a status of 1.

Based on that, your SQL query could look like this:

SELECT
  B.*
FROM
  B
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      A
    WHERE
      A.Status = 1
      AND B.Item = A.Item
      AND B.Date IN (A.Date1, A.Date2, A.Date3, A.Date4)
  )
;

So, if table A has no match on Item and Date at all, the NOT EXISTS predicate will evaluate to True. If there is a match and its status is 0, the predicate will again be true because of the Status condition.


Why did adding a secondary index not increase select performance? I have a table in MySQL called messages, which looks like this :

id (primary key)   | description    |  created_at(timestamp)

This table is supposed to hold chat messages between users of my application. As a result, the number of write operations on it will be high.

The application has an API that returns all the messages between two time stamps. This query will be fairly common too, but less than the number of write operations.

I ran mysqlslap with 100 concurrent connections and around 15000 rows in that table, and got a total time of around 8.6 seconds.

Then I added a secondary index on created_at, hoping to get the results in much less time for my search between two times, but I got an increase of 0.3 seconds for the same inputs.

Why did I not see a significant performance increase?

Edit:

this is what my table looks likes :

DROP TABLE IF EXISTS `mssg`;

CREATE TABLE `mssg` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `body` MEDIUMTEXT NULL DEFAULT NULL,
  `length` VARCHAR NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

and this is how i added the index:

ALTER TABLE testalter_tbl ADD INDEX (created_at);

Typically, there will be around 150 to 350 messages returned from the select statements

I'll skip my comments about that CREATE TABLE statement that would be classified from wrong to insulting, like having all columns declared NULL (what would a row with (NULL, NULL, NULL, NULL) mean?) and concentrate on the question asked.

The difference between 8.6 and 8.9 seconds is negligible. For all we know, the running times of the 2 tests are almost identical. If you want to test properly, you should run longer tests and with larger size of the table (try with 15K, 150K, 1500K, 15M rows) and see whether the efficiency changes or stays the same between having or not the specific index.

You should also examine the execution plans created for the queries in the 2 cases (at various table sizes), to see what is happening behind the scenes, what indexes are used, if any, etc.

My prediction would be that as the table grows larger, the index will get more useful, as the queries will require an index seek to find the (IDs of the) rows in the wanted time range and then some additional seeks from the clustering PK index (I assume you use InnoDB.)

Without the index, a whole table scan will have to be done each time. The larger the table gets, the biggest the difference you'll see between the 2 test cases (with and without the index).

If the vast majority of your queries is similar to the following, getting rows from a small time range:

SELECT <columns_wanted>
FROM mssg
WHERE created_at >= @start_timestamp
  AND created_at < @end_timestamp ;

then I would suggest (assuming you use InnoDB) an alternative design: make the (created_at) the clustering key of the table. Since you may have 2 or more rows with exact same timestamp, you can set the primary key to (create_at, id) for this effect (as InnoDB allows only unique keys for clustering). You will also need an additional index on (id) for the AUTO_INCREMENT to be allowed. This can be declared UNIQUE or not, it's your choice:

CREATE TABLE mssg (
  mssg_id INTEGER NOT NULL AUTO_INCREMENT,
  body MEDIUMTEXT NULL,
  length VARCHAR(20) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (created_at, mssg_id),
  -- UNIQUE
    INDEX mssg_id_ix (mssg_id)             -- this is needed for the AutoIncrement
);

This way, all the rows that your queries need will be stored in consecutive pages of the clustering (PK) index and the efficiency of the queries - as long as they ask for a small time range - will not be affected by the size of the table.

Further comments, partially related to the question;

Why are all columns declared as NULL? I suggest you change them all to NOT NULL except for those columns that there is a specific reason to allow nulls.
Why mediumtext for the message? Are you going to store so large messages? It might be better for performance, if you could reduce the size, to say VARCHAR(250).
Why is the length declared as VARCHAR and not INTEGER?
Further, if it's going to be storing the length of the text, it is not needed at all. You can get the length at any time using the proper text function.
You could declare the TIMESTAMP column to get a default CURRENT_TIMESTAMP if you want (if it is not already provided by the application.)


Always On best practice for secondary node maintenance In my AG, I have two nodes using synchronous commit, automatic failover, allow all connections in primary role, and readable secondary as yes.  The failover cluster is using a fileshare witness as the quorum.  I have to reboot the secondary replica tonight.

What is the best practice for handling this type of maintenance?  Since I have quorum, can I just reboot the secondary?  Do I need to drain roles of the secondary in the cluster manager?  Is there an AG maintenance mode?

No AG maintenance mode. You can restart secundary node and you will get events in logs, both SQL server and cluster, but nothing else. If you need to restart primary, first do an AG manual failover and check everything went fine.


PosgreSQL: setting high work_mem does not avoid disk merge This is not quite my day with postgres. On my server machine with PosgreSQL 9.2.3 I have set work_mem to 4MB to avoid Sort Method: external merge  Disk: 2072kB but it did not help:

cwu=# vacuum analyze web_city;
VACUUM
cwu=# SHOW work_mem;
 work_mem
----------
 4MB
(1 row)
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=18304.35..20487.34 rows=95562 width=10) (actual time=1557.871..1809.029 rows=64459 loops=1)
   ->  Sort  (cost=18304.35..18633.84 rows=131796 width=10) (actual time=1557.856..1707.069 rows=131796 loops=1)
         Sort Key: ("left"((name)::text, 5))
         Sort Method: external merge  Disk: 2072kB
         ->  Seq Scan on web_city  (cost=0.00..4842.45 rows=131796 width=10) (actual time=1.050..174.907 rows=131796 loops=1)
 Total runtime: 1828.936 ms
(6 rows)

Setting work_mem to 8MB finally helps:

cwu=# SET work_mem = '8MB';
SET
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=5501.43..6675.72 rows=93943 width=10) (actual time=207.628..244.667 rows=64459 loops=1)
   ->  Seq Scan on web_city  (cost=0.00..4842.45 rows=131796 width=10) (actual time=0.749..102.511 rows=131796 loops=1)
 Total runtime: 263.154 ms
(3 rows)

But why 4MB is not enough? In postgres wiki, there is this note:

  if you see a line like "Sort Method: external merge Disk: 7526kB" in
  there, you'd know a work_mem of at least 8MB would really improve how
  fast that query executed, by sorting in RAM instead of swapping to
  disk.

So I assumed it will be the same in my case.

EDIT: If I do:

cwu=# create index name_left_prefix on web_city(left(name, 5));

then 4MB is finally enough. It seems that the index causes lower memory usage. If anyone would be that kind to explain all this behaviour I would be very grateful.

This is somewhat speculative but Depesz (Hubert Lubaczewski) has this to say on the subject:

  You might wonder, though, why PostgreSQL switched to Disk, when it
  used only 448kB? After all, work_mem is 1MB. Answer is pretty simple –
  as I understand – disk is used when work_mem is not enough, so it
  means it's already been filled. So, sort with “Disk: 448kB" would mean
  that more or less whole work_mem has been used plus 448kB of disk.

So in your case the used work_mem might be in the 6 MB range. Also, try reset work_mem first, maybe there's stuff in there from a previous query.


Lock hints on preconditions Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data?

(This is a contrived example, of course, to illustrate a scenario in which a delete/update can only be performed dependent upon the state of some other table)

-- Schema
CREATE TABLE EntityTypes (
    EntityTypeID int NOT NULL,
    Name nvarchar(50) NOT NULL,
    CONSTRAINT PK_EntityTypes PRIMARY KEY CLUSTERED (EntityTypeID)
);

CREATE TABLE Entities (
    EntityID int IDENTITY(1,1) NOT NULL,
    EntityTypeID int NOT NULL,
    EntityName nvarchar(50) NOT NULL,
    CONSTRAINT PK_Entities PRIMARY KEY CLUSTERED (EntityID)
);

-- Test data
INSERT INTO EntityTypes (EntityTypeID, Name) VALUES (1, 'Test 1');

-- Updater 1
SET XACT_ABORT ON;
BEGIN TRAN;

IF EXISTS (SELECT 1 FROM Entities WHERE EntityTypeID = 1)
BEGIN
    RAISERROR('Cannot delete entity type which is referenced', 16, 1);
    RETURN;
END

DELETE FROM EntityTypes WHERE EntityTypeID = 1;

COMMIT TRAN;

-- Updater 2
INSERT INTO Entities (EntityTypeID, EntityName) VALUES (1, 'Test Entity')

Environment: SQL Server 2012

Assuming EntityTypeId is the primary key of dbo.EntityTypes then REPEATABLE READ would suffice:

SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;

IF EXISTS (SELECT 1 FROM Entities WHERE EntityTypeID = 1)
BEGIN
    RAISERROR('Cannot delete entity type which is referenced', 16, 1);
    RETURN;
END

DELETE FROM EntityTypes WHERE EntityTypeID = 1;

COMMIT TRAN;

The NOT EXISTS syntax Aaron suggested would also function as you require at REPEATABLE READ. The same would be achieved by specifying a hint on dbo.Entities:

DELETE et
FROM dbo.EntityTypes AS et
WHERE EntityTypeID = 1
AND NOT EXISTS
(
  SELECT 1
  FROM dbo.Entities WITH (HOLDLOCK)
  WHERE EntityTypeID = 1
);


How do I list all databases and tables using psql? I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool.

When I log in with psql --username=postgres, how do I list all databases and tables?

I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.

Please note the following commands:

\list or \l: list all databases
\dt: list all tables in the current database using your search_path
\dt *.: list all tables in the current database regardless your search_path

You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects).

To switch databases:

\connect database_name or \c database_name

See the manual about psql.


PostgreSQL: Force data into memory Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?

Postgres 9.4 finally added an extension to preload data from relations into the OS or database buffer cache (at your choice):

pg_prewarm

  This allows reaching full operating performance more quickly.

Run once in your database (detailed instructions here):

CREATE EXTENSION pg_prewarm;

Then it's simple to preload any given relation. Basic example:

SELECT pg_prewarm('my_tbl');

Finds the first table named my_tbl in the search path and loads it to the Postgres buffer cache.

Or:

SELECT pg_prewarm('my_schema.my_tbl', 'prefetch');

  prefetch issues asynchronous prefetch requests to the operating
  system, if this is supported, or throws an error otherwise. read
  reads the requested range of blocks; unlike prefetch, this is
  synchronous and supported on all platforms and builds, but may be
  slower. buffer reads the requested range of blocks into the database
  buffer cache.

The default is buffer, which has the greatest impact (higher cost, best effect).

Read the manual for more details.
Depesz blogged about it, too.


Enable Replication/CDC from High Availability Group I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis.

Background:
We have an Application Database Server which is on High Availability (Primary & Secondary Nodes). Our Staging Database Server will be communicating with the HA Group either through a Load Balancer or Listener.

Requirement:
We would like to enable Replication at the Table level and not the Database level from HA Group to the Staging Server as we are only interested in few tables. Is this even possible? If so, I am looking for some scripts (from your Toolbox, if you have any) to create Publisher, Distributor, and Subscriber and then disable/re-enable them on a regular basis during our Build process.

Note: This would be more of a "Pull" and I am looking for Scripts that can be executed from the HA Group as part of the Build Process.

Your help with this is very much appreciated.

Thanks!

Transactional replication should be able to do your requirement of replicate some specific tables, as long as they have primary key.

For CDC I have not used and can't say if will work or not.

Regarding the scripts you should be able to generate them when creating the replication setup.


Monthly report by time I work in a parking company. We need a report of monthly vehicle exits by time.

select hour(datetimeout) as 'dateout',count(*)
from to_leaveveh
where datetimeout between '2014-09-01' and '2014-09-02'
group by dateout

This only shows data for one day. I want to create a monthly report with:

Time in 24 hour format
Count of vehicle exits

Use the DATE_FORMAT function
SELECT DATE_FORMAT(datetimeout,'%H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <= ('2014-09-01' + INTERVAL 1 MONTH - INTERVAL 1 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%H:00:00');

or
SELECT DATE_FORMAT(datetimeout,'%H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <  ('2014-09-01' + INTERVAL 1 MONTH + INTERVAL 0 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%H:00:00');

Give it a Try !!!
UPDATE 2014-09-22 08:26 EDT
The queries I gave break down each by our hour of the month. You get no more than 24 rows.
If you want group by date and hour, then try this
SELECT DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <= ('2014-09-01' + INTERVAL 1 MONTH - INTERVAL 1 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00');

or
SELECT DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <  ('2014-09-01' + INTERVAL 1 MONTH + INTERVAL 0 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00');

Give it a Try !!!
UPDATE 2014-09-22 15:07 EDT
This will group by date and hour with a summary by date and an overall summary
SELECT
    IF(
        ISNULL(dt)+ISNULL(hr)=2,'Monthly Total',
        IF(ISNULL(hr)=1,CONCAT('Total for ',dt),HOUR(hr))
    ) Statistic,VehicleExitCount
FROM
(
    SELECT
        DATE(datetimeout) dt,
        DATE_FORMAT(datetimeout,'%Y-%m-%d') hr,
        COUNT(*) VehicleExitCount
    FROM to_leaveveh
    GROUP BY dt,hr
    WITH ROLLUP
) A;

Give it a Try !!!


Domain of Attributes for an Entity? I am new to DBMS and was reading about the ER Model and entities-attributes. I found the following line in the book which I was reading:

The domain of Composite attributes is the cross product of domains of
component attributes.

Which is fairly straight forward and intuitive. However, I was not able to understand the line following it:

The domain of Multi-valued attributes is a set of subsets of values
from the basic domain.

I couldn't understand this line. I know Power set, which is set of all subsets of a set. Does this line mean, The domain is the power set of the basic domain set? I tried searching on the web before posting, but couldn't find any related literature/ explanation.
A simple language explanation would help! Thank you!

I found my answer from the book "Database system concepts". Here's what that means:
Suppose C is a composite attribute of an entity type, which has 3 attributes : A1, A2, A3. Suppose, only A3 is multivalued.
Now say the domain of these 3 attributes contain 5, 4, 3 elements respectively.

The domain of Multi-valued attributes is a set of subsets of values
from the basic domain.

This means, the multi-valued attribute's (A3 in our case) domain will be the Power set of the basic domain set.
So, in our case, the power set of A3 will contain 2^3 = 8 elements. So, the composite attribute C will contain 5x4x8 = 160 elements.
In case A3 was not multi-valued, then the domain of C would contain 5x4x3 = 60 elements.


Is SQL Server data compression categorically good for read-only databases? Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required.  It also seems to imply that this is the primary downside to data compression, strongly implying that for a read-only archive database, the performance will (with few excep
tions) be improved by the usage of data compression of 100% filled pages.

Are the statements above true?
What are the primary "variations" between data compression and otherwise (for reading)

"CPU +x%"?
"IO -y%"?
page split occurence?
tempdb usage?
RAM usage?

And for writing?

For the purpose of this question, you can limit the context to PAGE-level compression of a big (> 1TB) database, but additional comments are always welcome.

References:

SQL Server Storage Engine Blog (The DW scenario shows compression to be very advantageous)
Data Compression: Strategy, Capacity Planning and Best Practices

  A more detailed approach to deciding what to compress involves
  analyzing the workload characteristics for each table and index. It is
  based on the following two metrics:

  U: The percentage of update operations on a specific table, index, or partition, relative to total operations on that object. The lower
  the value of U (that is, the table, index, or partition is
  infrequently updated), the better candidate it is for page
  compression.
    S: The percentage of scan operations on a table, index, or partition, relative to total operations on that object. The higher the
  value of S (that is, the table, index, or partition is mostly
  scanned), the better candidate it is for page compression.

Both of the above are demonstrably biased towards recommending page compression for DW-style databases (read-intensive/exclusive, big-data operations).

Just my 2cents from my own experiments on 1-2 year old hardware:

Read-only operations (DW-style scans, sorts etc) on page-compressed tables (~80rows/page) I've found to break-even at compression size reduction of ~ 3x.

I.e. if the tables fit into memory anyway, page compression only benefits performance if the data size has shrunk by over 3x. You scan fewer pages in memory, but it takes longer to scan each page.

I guess your mileage may vary if your plans are nested-loop and seek-heavy. Among others, this would also be hardware-dependent (foreign NUMA node access penalties, memory speed etc).

The above is just a rough rule-of-thumb that I follow, based on my own test runs using my own queries on my own hardware (Dell Poweredge 910 and younger). It is not gospel eh!

Edit: Yesterday the excellent SQLBits XI presentation of Thomas Kejser was made available as a video. Quite relevant to this discussion, it shows the 'ugly' face of CPU cost for page compression - updates slowed down by 4x, locks held for quite a bit longer.

However, Thomas is using FusionIO storage and he picked a table that is only 'just' eligible for page compression. If storage was on a typical SAN and the data used compressed 3x-4x then the picture might have been less dramatic.


PostgreSQL user only drop some tables Is there a way to have a PostgreSQL user that can drop all but specified tables?

We need the user to be able to drop some tables but not all. It would be preferred to be able to use something like revoke where we could specify which tables the user should not be able to drop.

Since you're running 9.3, you could potentially use an event trigger to catch the tag DROP TABLE and prevent that for certain users.

Denish Patel has a example in his blog post here Preventing Human Errors in Postgres. Search for the string "Function to prevent single table drop". That should give you enough of a start to modify for your specific needs.


Amazon RDS Ola Hallengren Very new to SQL on AWS RDS so.

Need to create maintenance jobs on an AWS RDS SQL2016 instance.  I know parts of Ola Hallengren's life saver maintenance script works, Index, stats and integrity.

Picking through Ola's maint script it looks like there is logic that checks for RDS.

For anyone who has done this, how do I install it, do I run the scripts individually and against which DB?

The stored procedure DatabaseBackup is not supported on Amazon RDS.

As you note, there is logic that checks for the RDS platform.

If these checks return that your server is an RDS instance the following code block is encountered.

  IF @AmazonRDS = 1
  BEGIN
    SET @ErrorMessage = 'The stored procedure DatabaseBackup is not supported on Amazon RDS.'
    RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT
    SET @Error = @@ERROR
    RAISERROR(@EmptyLine,10,1) WITH NOWAIT
  END

Pretty self explanatory, eh? Sorry about that

You're probably best served starting by browsing AWS docs for SQL Server backups on RDS


How to relate LatLong and State columns on PowerBI I have two separate tables, one with latitude and longitude columns (for locations in the USA), and the other with the US state abbreviation (TX, AZ, CA, etc).

Is there a way to count the number of rows in the LatLong table that fall within each state in the other table using PowerBI?

Without ever using PowerBI, without even knowing what it is, no. Highly unlikely.

You'll have to pull in Shapefiles that represent the states from the Census or like data-source. Then you'll have to construct points representing the cords and run point-in-polygon on the result.

Check out

Geographic Information Systems GIS.StackExchange


Migrating a maintenance plan from ms sql 2008 to 2016 I have a bunch of maintenance plans that I want to copy/migrate to sql server 2016. I am able to connect to Integration Services on sql server 2016 but I get the following error when expanding 'MSDB' folder under 'Stored Packages' folder in Integration Services:

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 R2 Books Online.
Login failed for user 'X'. (MsDtsSrvr)

I assume the configuration is ok since the plans have been there for years now. Is there other ways to deal with maintenance plans without having integration services involved?

It might not be the exact answer you're looking for, but consider using jobs instead of maintenance plans. Ola hallengren has a full (free) maintenance solution that you can download and deploy on your server. ola.hallengren.com It's used by a lot of DBAs, and has been around for years.

All you have to do is deploy the scripts and add some schedules, you can also fine tune everything to fit your needs. There's code examples and options on the Ola site.

A big benefit you get from this, is that you can simply script out everything, and deploy it on any server you want, without much hassle.

It might be worth your time checking this out.


Update value in timestamptz column I have a table column with the data type timestamptz. Time zone of server is America/Denver. Going through some strange issue while inserting a value in that column.

When I update the column to the value '03/11/2018 02:00:00' it results in '03/11/2018 03:00:00' (+ 1 hour!).

UPDATE details
SET    interval_start_timestamp = '03/11/2018 02:00:00'::TIMESTAMP
WHERE  id = 2395

The issue is only with the time 02:00:00. 02:30:00 and other values like 01:00:00 or 01:30:00 are inserting properly.

PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

Any one can help me to understand this behavior?
And how can I insert the proper values?

You fell victim to a cascade of bad ideas.

1. DateStyle

Do not use locale-dependent format in your statements if you can avoid it. If locale settings change (lc_time and DateStyle in particular), your statement suddenly does something different. '03/11/2018' is ambiguous unless coupled with your specific settings. Always use ISO format which is unambiguous and does not depend on additional settings: '2018-03-11'.

2. timezone

Do not assign a timestamp value (or literal) to a timestamptz column. The assignment cast depends on the current timezone setting of your session which introduces another dependency. With a different timezone setting your statement does something different. Use an explicit cast or a timestamp with time zone literal (timestamptz). In your case:

UPDATE details
SET    interval_start_timestamp = '2018-03-11 02:00:00'::timestamp AT TIME ZONE 'America/Denver'
WHERE  id = 2395

Using an actual time zone name (not an abbreviation) accounts for DST reliably - if your OS is up to date as Postgres works with information provided by the underlying OS.

Related:

Ignoring timezones altogether in Rails and PostgreSQL
Oddities with `AT TIME ZONE` and UTC Offsets

3. DST

Which results in '2018-03-11 03:00:00'. As ypercube pointed out, you hit the start of Summer Time a.k.a. Daylight Savings Time. Clocks were turned forward 1 hour at this time. Each time between 02:00:00 and 02:59:59.999999 is interpreted to mean something between 03:00:00 or 03:59:59.999999 respectively, by the silly rules of DST.

The mere existence of "daylight saving time" (DST) is an insult to reason. It should be abolished and never be spoken of again (except for historic time values doomed to mess with this forever).

Test:

SELECT '2018-03-11 02:34:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Denver' AS t2
     , '2018-03-11 03:34:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Denver' AS t3;

t2                  | t3
:------------------ | :------------------
2018-03-11 03:34:00 | 2018-03-11 03:34:00

dbfiddle here


DR site SQL Server DB server not need Quorum Disk? We have two SQL Server DB Servers and Shared disk in production site and 1 DB in DR Site.

Production site has Quorum Disk on Shared Storage.
Production site DB servers can access this share storage, but DR site SQL DB Server can't access it.

If Production site SQL DB Servers are down, will Windows Server Failover Cluster be Online or Offline?

Does DR Site SQL Server need Quorum disk?

If Production site SQL DB Servers are down, will Windows Server Failover Cluster
  be Online or Offline ?

If you have a setup that relies on a quorum disc, this must be available - ergo offline. This is a non recommended setup. Whoever set it up did not bother to validate it makes sense.

Geographically dispersed clusters are a proven technology (start at https://technet.microsoft.com/en-us/library/cc757840(v=ws.10).aspx) but their setup was seriously bothered here.

"The quorum data must be synchronously replicated between the sites. To ensure that the Server cluster guarantees of consistency are met, the cluster database must be kept consistent across all nodes. If the quorum disk is replicated across the sites, it MUST be replicated synchronously."

Simple like that.

If i were you, I would start retiring the error prone outdated approach and work towards AOAG with a read only replica offsite.

https://www.simple-talk.com/sql/database-administration/sql-server-2012-alwayson/

This eliminates the shared storage even locally (which IS a problem - shared storage means a dying server crashing the database structure takes down the cluster). The DR scenario is a complicated one, though. You likely end up having to have the quorum disc replicated.


Startup Parameters greyed out in SSCM for new instance We do not appear to be able to change the startup parameters for a new SQL Server instance, using SQL Server Configuration Manager (see screenshot below).

We are members of the sysadmin fixed server role, and are running SSCM version 2015.0130.16111.04 for Microsoft SQL Server 2016 (SP2-CU6) (KB4488536) - 13.0.5292.0 (X64) Enterprise Edition.

As stated in the comment, please check your privilege. This change you are trying to make will write startup parameters to the registry and need privilege beyond member of sysadmin role in SQL Server.

Ref: SCM Services - Configure Server Startup Options

Limitations and Restrictions

  SQL Server Configuration Manager writes startup parameters to the
  registry. They take effect upon the next startup of the Database
  Engine.

  On a cluster, changes must be made on the active server when SQL
  Server is online, and will take effect when the Database Engine is
  restarted. The registry update of the startup options on the other
  node will occur upon the next failover.

Security

Permissions

Configuring server startup options is restricted to users who can
change the related entries in the registry. This includes the
following users.

Members of the local administrators group.
The domain account that is used by SQL Server, if the Database Engine
is configured to run under a domain account.


How to store daily records in SQL Server 2017 database? So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries.

Example:

and so on...

Now, as the application will grow, the nummber of users will also increase, thus, making millions of records. I have to fetch monthly, yearly quarterly data from this table, which will take too much time to fetch whatever query I will use. So, I want to ask what can I do to speed up the process of fetching the data fastly?
Also, can anyone please tell me what better design can be given to this table?

NOTE: I am a beginner in Database work and has very little and limited knowledge.

Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light.

You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.


Instead of trigger Sqlserver and Inserted table I have this trigger:
create TRIGGER Emp_log on Emp
Instead of insert as
SELECT * FROM INSERTED

Then i make this insert:
insert into emp
values(99,'bbb','aaa','99','1900-09-09',99,99)

As a result no line was inserted in the emp table, as it should be.
But anyway i got the display of the inserted row with the command SELECT * FROM INSERTED.

The question is: The "INSERTED" table contains what is written in the insert statement, or contains rows actually inserted?

Because I expected no insert, and then no display from SELECT * FROM INSERTED.

Thank You

Correct, that is because the Trigger is just part of the entire transaction.  The INSERTED table will hold the records that would have been inserted.

The difference between AFTER triggers, which may be causing this confusion, is that INSTEAD OF doesn't verify if the row insert would have caused failures due to any constraints present on the table. If an insert causes a PK violation for instance, the INSTEAD OF trigger would still fire, the AFTER trigger would not.

Typically, Instead Of triggers are used to perform additional validation or calculations on the data (from the inserted table) before doing the final insert or rollback or raiserror depending on the business logic you are trying to implement.


what is better in terms of allocated space in Oracle Tables: NUMBER or BINARY_FLOAT? I have a 10 billion records table so I need to improve space allocation. My table has only fields of type floating point (single-precision). What is the best choice, NUMBER or BINARY_FLOAT or something else ?

It depends on your data (and this assumes that you don't care about the different behaviors of the two data types).  You can put together a test with sample data that is similar to what you're actually storing to find out what approach will use less space.

I'll create a table num_test with two different columns, one declared as a number and one as a binary_float.

create table num_test (
  num_col number,
  flt_col binary_float
);

If I populate it with 100,000 rows of data in increments of 0.001

begin
  for i in 1 .. 100000
  loop
    insert into num_test( num_col, flt_col )
      values( i/1000, i/1000 );
  end loop;
end;

then I can see the minimum, maximum, and average size required to store the values

select min( vsize(num_col) ), max( vsize(num_col) ), avg( vsize(num_col) ),
       min( vsize(flt_col) ), max( vsize(flt_col) ), avg( vsize(flt_col) )
  from num_test;

If you run that, you'll see that the number column is averaging 3.89 bytes/ row (with a min of 2 and a max of 4) while every binary_float required 4 bytes of storage.  So there is a small savings to using number in this case.

But is that the whole story?  Nope.  If we use different sample data, we get very different results.  Let's run the same test but divide everything by 3

begin
  for i in 1 .. 100000
  loop
    insert into num_test( num_col, flt_col )
      values( i/1000/3, i/1000/3 );
  end loop;
end;

Now, if you run the same vsize query, you'll see that the number column takes up to 21 bytes of storage and averages 7.69 bytes for the 200,000 rows.  The flt_col is still using a fixed 4 bytes for every row.

Depending on your data, you may find that one or the other approach uses more space.  Of course, where there are substantial differences in space utilization, there is the potential for more substantial rounding issues when you move to floating point numbers.

Note as well that the results may be version-dependent.  I did my test on an 11.2 database where the documentation indicates that binary_float always takes 4 bytes of storage.  In 10.2, the documentation indicates that binary_float took 5 bytes due to the inclusion of an extra length indicator.  It's not obvious to me why a 32-bit value would ever need a length byte so I'm hard-pressed to understand why an earlier version of Oracle might have required the fifth byte, but the documentation is consistent that it did.


Can a TRUNCATE or DROP table ever be very time consuming? A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day.

It was a top candidate for removal. However, I never got the opportunity to achieve this (a.k.a. not the priority). I did not even have the opportunity to try to delete it in a weekly refreshed dev database.

I was wondering whether a simple TRUNCATE or DROP could ever be very time-consuming? I mean, could this operation block a production environment for several minute like DELETEs can when they are not well written. Or are these safe operations?

MyISAM -- DROP is like a file delete.  TRUNCATE performs the C file action of removing all the data but leaving the file in existence.
InnoDB with its own tablespace (cf innodb_file_per_table) -- like a file delete
InnoDB without its own tablespace -- lots of blocks to free up; this could take time.

A file drop can be fast or not so fast, depending on the Operating system.  Some OS's run around freeing up the blocks right then and there; you have to wait.  And, because it is an OS operation, this may interfere with virtually anything else.  What OS?

"Minutes" seems long.  How many minutes?  How many GB?


How to backup MySQL accross multiple servers I have a few questions related to MySQL security.

How do we backup the data in MySQL database? I know there's an output function where you can export your data in the db as sql statements. Is this the only way that we can backup the data?
Is there a way where we can backup the data automatically without manually does what I mentioned above?
What if I have two different servers. Let's call A and B. And A is the main server, B is the backup server. And how can I transfer my CRUD requests to the B server when A is down?

Backup Solution: There are several tools and methods to perform Backup & restore operations. You can refer official documents for more information. If you want to use third party tool then Percona XtraBackup is the most popular tool.

Automatic Backup: You need to create schedulers to take backup by executing the backup scripts. If you are using the linux based servers then it is recommended to create incremental backup scripts, executed by CRON jobs on scheduled interval.

Failover Handling: To take care of failover event ( i.e. main server is down ) you need to prepare high availability configuration, so that backup server can take place of main server. You can read official guide about it.

I prefer clustering based solution to take care of High Availability:


How to Migrate Oracle 11g R2 (Windows) database to Oracle 12c R2 (Linux) db I have a requirement to migrate Oracle 11g R2 database (Windows platform) to Oracle 12c R2 database (Red Hat Linux platform).

Is there any methods using RMAN to do it?

Any help will be appreciated.

Source:

RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup database ...

Destination:

RMAN> startup nomount
RMAN> restore controlfile from ...
RMAN> alter database mount;
RMAN> restore database;
RMAN> alter database open resetlogs upgrade;

Then manually upgrade the database to 12.2:

Example of Manual Upgrade of Windows Non-CDB Oracle Database 11.2.0.3


Encrypted data using MS SQL SERVER 2008 R2 ENTERPRISE EDITION. Can we decrypt data using 2008 R2 STANDARD VERSION? I have a database backup which is encrypted by using MS SQL SERVER 2008 R2 Enterprise Version. I now need to decrypt the data. My company cannot afford license for MS SQL Server Enterprise Edition as it is costly. Can we decrypt database backup on MS SQL 2008 R2 Standard version? Or any other cost effective solution is greatly appreciated.

Since the only way to encrypt backups natively in SQL Server 2008 R2 is to use Transparent Data Encryption. You will need to back up the TDE certificate and private key used for TDE in the master database and restore it in the master database of either an Enterprise Edition of SQL Server which is equal to or higher version number or an instance of Developer Edition of equal or higher version. A free option for you is to install SQL Server 2016 Developer Edition and restore to that.

If you used third party software, you will need to refer to the documentation for that product.

If this were SQL Server 2012 or above, then what matters more is the version, which will be in the format 12.0.4422.0, not the edition and also if you have used Enterprise only features in your database. Use the SQL statement below to check:

SELECT * FROM sys.dm_db_persisted_sku_features;

If the above SQL returns any rows, then you can't restore on Standard, but there are other options. You can backup databases on Enterprise and restore them to Developer Edition, which supports all Enterprise features, if you want, but you can't restore a backup from a higher version and restore it to a lower version. If the database is less than 10GB, then you can also restore it to an instance of SQL Server Express, which is free and will support restoring encrypted backups but will not support taking encrypting backups. You will also need to backup the certificate and private key from the master database on your Enterprise system and restore it to your target system assuming you are not using an Extensible Key Management system.


Understanding Query Plan’s “width” The Douglas and Douglas PostgreSQL book includes the following EXPLAIN result:

The first step of the query, the Seq Scan, shows a width of 1917. However the last step of the query, the Sort, has a width of 1911.

Why/how could these values differ?

Every PostgreSQL row version (“tuple”) has a number of system columns and other data that are not displayed by SELECT * (see the documentation for details. Among them are

xmin and xmax
extra flags that contain the “hint bits” and the “frozen” flag.
ctid, the tuple's physical address.

These would account 23 bytes, not for four, so you'd have to look at tge source for more. The only of these items that occupies 6 bytes is ctid, so maybe that.


Are there disadvantages to using Full-Text Search in SQL Server? Are there disadvantages to using Full-Text Search in SQL Server? Is there much of an increased CPU or memory load on the server?

Read this: SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned.


Create sequence based on the value of another column I have to create sequence numbers based on related user. Like:

╔════════════╦═════════════╦═══════════════════╗
║ Row Id     ║ User Id     ║ Sequence Number   ║
╠════════════╬═════════════╬═══════════════════╣
║ 1          ║ 1           ║         1         ║
║ 2          ║ 1           ║         2         ║
║ 3          ║ 2           ║         1         ║
║ 4          ║ 2           ║         2         ║
║ 5          ║ 3           ║         1         ║
║ 6          ║ 1           ║         3         ║
╚════════════╩═════════════╩═══════════════════╝

I have some prerequisites:

Each sequence number sholud be between 1 and 9999. After 9999, it shoud leap to 1 and continue from there. (CYCLE property of Sequence)
Sequence numbers should be generated based on User Id. Each UserId should have its own sequence.
Sequence numbers should be sequencial. such that for User ID 5: Sequence # 123 sholud be followed by 124, leap or reuse of a number sholud not happen.

So using a Sequence looks pretty right to me. But I failed to add User ID distinction to sequence.

CREATE SEQUENCE [dbo].[seq_SequenceNumber]
AS [smallint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
CYCLE
NO CACHE

How can I add UserId partition to this sequence?

This will produce the desired result for a single query.

drop table if exists T1;
go

create table T1(RowId int, UserId int, Desired_SequenceNumber int);
go
insert T1
values
(1, 1, 9997),
(2, 1, 9998),
(3, 2, 1),
(4, 2, 2),
(5, 3, 1),
(6, 1, 9999);   -- Have increased User 1's values to test wrap-around.
go

select
    T1.*,
    SequenceNumber = ((ROW_NUMBER() over (partition by UserId order by RowId) - 1) % 9999) + 1
from t1
order by RowId;

The ROW_NUMBER built-in function creates a sequential integer for each row in the result. partition by UserId ensures each value of UserId starts counting again at 1. Order by RowId ensures the ordering of SequenceNumber matches the ordering of RowId. The - 1) % 9999) + 1 bit ensures wrap-around at 9999 and starting again at 1, rather than at zero.

If the SequenceNumbers must be written once and consistent for all subsequent reads the above will not work. Then you will need something like

;with NewData as
(
    select *
    from
    (
        values
            (7, 1),
            (8, 4)
    ) as T(RowId, UserId)
),
LatestRow as
(
    select
        UserId,
        RowId = MAX(RowId)
    from T1
    group by UserId
),
NextSequence as
(
    select
        T1.UserId,
        Number = (T1.Desired_SequenceNumber + 1) % 9999
    from T1
    inner join LatestRow
        on LatestRow.RowId = T1.RowId
)
-- insert T1(RowId, UserId, Desired_SequenceNumber)
select
    NewData.RowId,
    NewData.UserId,
    Desired_SequenceNumber = ISNULL(NextSequence.Number, 1)
from NewData
left outer join NextSequence
    on NextSequence.UserId = NewData.UserId;

I've structured it using CTEs for ease of exposition. It could be re-factored for performance.

CTE NewData introduces test data without having to manage tables. Replace this with your actual source. Notice that RowId 7 is for an existing user and 8 is for a new user. I've hard-coded RowIds for ease of reference. This is not necessary for this solution. Your tables likely use an IDENTITY and that's OK.

Because the numbers wrap-around the solution must find the most recent value, not the largest value, and increment from there. I use RowId as a proxy for time. CTE LatestRow finds the most recent i.e. largest / MAX() RowId for each existing user. If RowId is not monotonic this solution will not work and some other measure of time will be required. Note that gaps are OK so deletions and IDENTITY / SEQUENCE is acceptable. Depending on your cardinalities performance may be improved by INNER joining to NewData in this CTE.

CTE NextSequence maps the RowId to an existing sequence value and calculates the next sequence number. The modulo (%) does the wrap-around at 9999. I changed my sample data from that in your question so UserId 1 will wrap around. I'm assuming one row per user in the new data. If there are more you can can assign each in-coming row a local sequence within the new data and add & modulo to the existing base sequence number.

The final select gives the data to be inserted. If your real table has an identity omit RowId. Using an outer join allows new users to be inserted. The ISNULL sets new users' sequences to 1.

Since you get to name SEQUENCE objects you could, in a very convoluted way, create a new one for each value of UserId, and try to code which one to pull from for each in-coming new row. As an exercise it would be interesting. As a production system it would be appalling. Don't do this. The meta-coding would be awful and there will be some limit to the number of sequence objects available in a database.


Unable to update data from federated table automatically I am trying to transfer data from remote Server to localhost through MySQL.

In the remote server I created a database HOD and a table tab.

In the localhost I created the database "HOD" and a table "tab". I also created a stored procedure fed_procedure and federated table "fed_tab" to pull the data from remote table. The federated table "fed_tab" gets data from the remote server but the table "tab" updates only when I call the stored procedure using CALL fed_procedure(); I want the table "tab" to update automatically every 5 minutes. So I created an event schedule to update the table "tab" every 5 minutes. But when I execute it, I am getting the error :Access denied for user 'root'@'%' to database 'HOD'". How can I do this?

Below is the stored procedure I created and the event scheduler.

DELIMITER $$

DROP PROCEDURE IF EXISTS `HOD`.`fed_procedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fed_procedure`()
BEGIN

  declare tab_indx integer(11);

  set tab_indx = (SELECT tab_ndx FROM tab order by tab_ndx desc limit 1);
  insert into tab select * FROM fed_tab where tab_ndx>tab_indx;

END $$

DELIMITER ;

The event scheduler is below

CREATE EVENT fed_procedure ON SCHEDULE EVERY 5 minute
do
CALL fed_procedure()

You can create an event as follows for calling stored procedure in every 5 minutes.

CREATE EVENT myEvent
ON SCHEDULE EVERY INTERVAL 5 minute
DO CALL myProcedure();

Here i am creating full process of executing an event with example.

step 1: checking user permissions.user should contain grant permission as     follows.

   show grants for root@'localhost'\G
   Result:`GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION.
step2: creating an database with two tables as you mentioned tab and fed_tab.

step3: Creating and executing  stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `HOD`.`fed_procedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fed_procedure`()
BEGIN

  declare tab_indx integer(11);

  set tab_indx = (SELECT id FROM tab order by id desc limit 1);
  insert into tab select * FROM fed_tab where id>tab_indx;

END $$

DELIMITER ;

step4: Creating an event.

 CREATE EVENT myEvent
ON SCHEDULE EVERY  5 minute
DO CALL fed_procedure();

it is successfully executing in my machine.the main problem of yours is permission of root user.


PostgreSQL extremely slow after upgrade from 9.3 to 9.4 We recently upgraded an Amazon RDS instance from PostgreSQL 9.3 to 9.4. There were no problems during the upgrade, and afterwards we ran VACUUM ANALYZE. Performance is now very slow across the board. Here's an example of a query that took a fraction of a second in 9.3:

EXPLAIN ANALYZE SELECT room_name, id FROM common_activityinstance WHERE started_by_id = 1370408 AND room_name IN ('robcv28', 'foobartest', 'noroster', 'jscode', 'cv28', 'johansencouple', 'lv426', 'johansenfamily', 'johansen') AND end_time IS NULL;
                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on common_activityinstance  (cost=55.63..59.65 rows=1 width=13) (actual time=1.082..1.082 rows=0 loops=1)
   Recheck Cond: ((started_by_id = 1370408) AND (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[])))
   Filter: (end_time IS NULL)
   Rows Removed by Filter: 925
   ->  BitmapAnd  (cost=55.63..55.63 rows=1 width=0) (actual time=0.349..0.349 rows=0 loops=1)
         ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..5.53 rows=146 width=0) (actual time=0.122..0.122 rows=927 loops=1)
               Index Cond: (started_by_id = 1370408)
         ->  Bitmap Index Scan on common_activityinstance_room_name_c1f9997a_like  (cost=0.00..49.85 rows=1171 width=0) (actual time=0.171..0.171 rows=926 loops=1)
               Index Cond: (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[]))
 Total runtime: 1.116 ms
(10 rows)

This same query is now much slower in 9.4:

EXPLAIN ANALYZE SELECT room_name, id FROM common_activityinstance WHERE started_by_id = 1370408 AND room_name IN ('robcv28', 'foobartest', 'noroster', 'jscode', 'cv28', 'johansencouple', 'lv426', 'johansenfamily', 'johansen') AND end_time IS NULL;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on common_activityinstance  (cost=10157.60..14038.97 rows=45 width=36) (actual time=3923.011..3923.011 rows=0 loops=1)
   Recheck Cond: ((started_by_id = 1370408) AND (end_time IS NULL))
   Rows Removed by Index Recheck: 698
   Filter: (room_name = ANY ('{robcv28,foobartest,noroster,jscode,cv28,johansencouple,lv426,johansenfamily,johansen}'::text[]))
   Heap Blocks: exact=634
   ->  BitmapAnd  (cost=10157.60..10157.60 rows=991 width=0) (actual time=3917.278..3917.278 rows=0 loops=1)
         ->  Bitmap Index Scan on common_activityinstance_started_by_id  (cost=0.00..3282.60 rows=198155 width=0) (actual time=0.245..0.245 rows=927 loops=1)
               Index Cond: (started_by_id = 1370408)
         ->  Bitmap Index Scan on end_time_id  (cost=0.00..6874.73 rows=198155 width=0) (actual time=3915.565..3915.565 rows=1674860 loops=1)
               Index Cond: (end_time IS NULL)
 Planning time: 2.457 ms
 Execution time: 3923.065 ms
(12 rows)

I don't understand why the query plans are different. And this is just one example—another query was running for 15 minutes before I just gave up and stopped it.

I thought maybe there was a problem with the indexes, so I also did REINDEX DATABASE but it didn't help.

Any ideas?

Since the entire database seemed to be affected, I decided to try a VACUUM (FULL, ANALYZE). It took 50 hours and 4 minutes to complete, but it was totally worth it. Our database disk usage went from 1259 GB down to 747 GB (reclaiming a whopping 512 GB of space), and performance across the board has returned back to the levels we were seeing before the upgrade.


How can I assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows I have a table from MySQL called References with 20 columns and 20 rows:

CREATE TABLE bop_ppy
( start_time DATETIME (6)
, end_time DATETIME (6)
, high DECIMAL (5,2)
, low DECIMAL  (5,2)
, close DECIMAL (5,2)
, vol DECIMAL (5,2)
, vol_avg DECIMAL (5,2)
, range_ DECIMAL (5,2)
, poc_ DECIMAL (5,2)
, va_h DECIMAL (5,2)
, va_l DECIMAL (5,2)
, va_range DECIMAL (5,2)
, tpot INT
, tpo_ab INT
, tpo_bl INT
, sf DECIMAL (5,2)
, tff DECIMAL (5,2)
, rf INT
, vty DECIMAL (5,2)
, dists INT);

The data is updated daily.

I need to compute the difference between consecutive rows, for each column, (except the start_time and end_time columns, which are incremented by 1 day) such that if the value on day 2 is higher than the value on day 1, we assign +1 to the difference; if the value on day 2 is less than the value on day 1, we assign -1 to that difference; if the value on day 2 is equal to the value on day 1, we assign 0 to that difference. The idea is to generate a new table/view that shows only the tallies, instead of the original values.

Each row corresponds to one day's data. I am using MySQL Workbench.

I have tried to first do a self join of the table and then assign dummy variables to the difference between consecutive rows, but so far this has not worked.I don't know how to make this work anymore. Please could someone help.

You can use user defined variables for this. You can store the value of the current row's column in a variable, when the next row is processed, the variable still holds the value of the "previous" row.

Here's how it works.
You need to initialize the variables you need first. You can do this in a subquery.

SELECT
*
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery

Like I already said, the rows are processed one after another. In a relational database there is no order, unless you specify it. This is very important here, when you don't specify an order, you might not get the previous row, but a random or simply wrong row.

SELECT
*
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery
ORDER BY the_column_that_defines_the_order

In the SELECT clause the order is also very important. When you assign first and then do your calculations, the variables actually hold the value of the current row, not the previous one.

SELECT
CASE WHEN @prev_value < your_column THEN 1
     WHEN @prev_value > your_column THEN -1
     ELSE 0 END AS your_comparison
, @prev_value := your_column
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery
ORDER BY the_column_that_defines_the_order

Okay, now we have a problem. The @prev_value value is NULL, when the very first row is processed. There are two ways to prevent this.

First, use the COALESCE() function to replace the NULL value with something else or the column you're comparing to.

CASE WHEN COALESCE(@prev_value, your_column) < your_column THEN 1
     WHEN COALESCE(@prev_value, your_column) > your_column THEN -1
     ELSE 0 END AS your_comparison

In your case that's actually not necessary, since it would result in the ELSE part anyway, which is probably what you want. I'm just mentioning it.

Second, assign the value of your choice in the initializing subquery.

SELECT
CASE WHEN @prev_value < your_column THEN 1
     WHEN @prev_value > your_column THEN -1
     ELSE 0 END AS your_comparison
, @prev_value := your_column
FROM your_table
, (SELECT @prev_value := your_column FROM your_table ORDER BY the_column_that_defines_the_order LIMIT 1) var_init_subquery
ORDER BY the_column_that_defines_the_order

When you want the columns to be in a different order, you can just put above query in a subquery.


Best way to get SQL Server databases on small capacity VPS I have several small sites and I've just rented a Windows VPS server with 1&1. It includes 2 "vCores", 4 GB RAM and 120 GB SSD.

It runs great for my needs but now I need to determine the best way to get several SQL Server databases running.

Questions:

Would SQL Server Express kill such a configuration, as far as using up my RAM and bandwidth?
I wasn't able to find anyone who cost effectively would rent me just some space on a SQL Server database. Are there any other options short of either purchasing another VPS to house my database?

I realize some will scoff at this as being woefully inadequate. But my needs are not that significant and my budget for this is rather small.

SQL Server Express Edition is limited to 10GB maximum database size (per database) and 1.4GB of buffer pool memory per instance, so you won't "kill" the server by running it if there's nothing else (or very little) running on it. However, if you're doing a lot of work with the database(s), you'll find yourself starved for memory and reading a lot of data from disk instead of caching in memory.

Bandwidth? That depends greatly on how much traffic you'll have going into & out of the server.

  I wasn't able to find anyone who cost effectively would rent me just some space on a SQL Server database.

Azure SQL Database is very similar to what you describe here. "Cost effective" is in the eye of the beholder.


Do databases optimize repeated similar query params on the same column? I have the following query:
SELECT * FROM table t
WHERE t.id IN :ids
AND t.id IN :allowedIds

(where the query params are later replaced)
Are these query params optimized by the database itself? I would like to avoid having to merge the two sets of ids together in code because it makes for poor readability. The first :ids constraint is what the user themselves passes on as a filter and the second :allowedIds constraint is what the user has access to. So I am hoping most databases, but Postgres in particular, would optimize this sort of thing.

I'll ignore the "most databases" in this question, otherwise I'd have to vote to close for lack of focus. Instead, I'll answer about PostgreSQL.
To "merge" the lists in your question, you'd have to build the intersection:
WHERE id IN (1, 2, 3, 4) AND id IN (3, 4, 5, 6)

is the same as
WHERE id IN (3, 4)

PostgreSQL doesn't do that automatically. PostgreSQL may choose to scan an existing index for one or both of the conditions, or it may go with a sequential scan, but the two conditions are not merged.
What is more, PostgreSQL will treat the two conditions as statistically independent and just multiply their selectivity, which may result in a bad estimate.
You'll very likely do better with a query that has a single IN list, which is the intersection of the original lists.
You asked about why the PostgreSQL team has not added this optimization into the code for PostgreSQL, so I have a couple of comments about that.  This is a rather unusual requirement (I had misunderstood the question at first), and when adding optimizer code for such requirements there is always a trade-off: while your query would benefit, many queries would have to pay the price in the shape of extra CPU cycles spent to test if the optimization applies or not.  Now query planning time is a rather sensitive area (it has to happen fast), so we are usually reluctant to add special processing for corner cases, particularly if they can be easily avoided by rewriting the query.
Also of note:  You could do the intersection at the SQL level if you are able to pass the lists as arrays and install the intarray extension which allows you to calculate the intersection between two (integer!) arrays. e.g. where id = any(:ids & :allowedIds)


MySQL – BLObs Hitting Performance One of my tables contains seven BLObs. Six of these refer to raw data and are typically 1.0 – 1.5Mb each. The seventh is the result of processing this data and can be nearly equal in size to the sum of the other BLObs but may be significant less if some of the data has been rejected for various reasons. The raw data was originally kept in a directory structure but there were some issues with logical naming of directories and also with someone changing some directory names. Therefore keeping all the data in the database would appear the correct way to do it. The raw data is needed in case a different algorithm is developed and it needs to be reprocessed. The problem is that as information is added the performance is degrading. If a copy is put on another server and the six raw data BLObs are deleted performance improves significantly. Research on the web suggests that the raw data is read even if it is not used in the query. Creating two tables, one for the raw data and the other for the result of processing, seems to be a solution but this would mean repeating some of the meta data which seems bad design. Is there a better way of tacking this?

Do not use ENGINE=MyISAM, that will lead to blobs being costly for any operation on the table.  Instead, use InnoDB.

"Vertical partitioning" is generally not needed, because of the way InnoDB effectively does it for you....

With InnoDB, there are 4 ROW_FORMATs; let's try to pick the optimal one.  In all cases, some, or all, of each blob is stored away from the row itself.  More in a minute.

But first, let's check the queries.  Do not use SELECT *; always specify only the necessary columns.  This avoids reaching into other blocks to get the blobs.

The ROW_FORMATs are redundant, compact, dynamic, and compressed.  For your case, I recommend either DYNAMIC or COMPRESSED.  These store none of the blob in the record itself; instead they have a 20-byte pointer to where the blob is stored.  Only if you need to touch the blob does it run off to that location.

COMPRESSED is the same as DYNAMIC, except for a complicated block-level compression technique.  Most benchmarks I see say you get about 2x compression.  This is not as good as you might get by compressing the blobs in the client.  For example, most 'text' compresses 3x.  But most image formats are already compressed, so compression is a waste.

So, I leave it to you to decide between dynamic and compressed.  If in doubt, test with your data and your queries.

You should have innodb_file_per_table=1 when you CREATE the table.  You may need BARRACUDA and you do need at least version 5.6; 5.7 would be better.

Two tables, one for raw, one for combined?  Maybe.  Given the benefits described above, there may not be any benefit in having two table.  However, if there is a lot of activity in one column, it might be better to move it to a separate table (plus 1:1 id, etc).  (This is especially important for things like "like/unlike" counters.)

Since you are working with bulky things, be careful that you do not have long-running transactions.  Such can lead to timeouts, etc.  Still, use BEGIN...COMMIT where appropriate.


Building Oracle XE docker image in Windows 10 Pro I'm trying to install Oracle XE 18c in a docker container in Windows 10. I have docker already installed, and I need to build the image as explained here.

I cloned the git repository, but I'm stuck on this step, as what I cloned are Linux commands and not Windows.

After cloning with:

git clone https://github.com/oracle/docker-images.git

If I cd to docker-images\OracleDatabase\SingleInstance\dockerfiles\18.4.0 to build the image I see the following sh comands, instead of exe or bat:

12/28/2019  01:36 PM             1,137 checkDBStatus.sh
12/28/2019  01:36 PM               905 checkSpace.sh
12/28/2019  01:36 PM                75 Checksum.xe
12/28/2019  01:36 PM             2,752 Dockerfile.xe
12/28/2019  01:36 PM               533 oracle-xe-18c.conf
12/28/2019  01:36 PM             6,132 runOracle.sh
12/28/2019  01:36 PM               775 setPassword.sh

How to build this image in Windows?

Use Wsl Ubuntu Bash set path to Docker executables or simply install Git Bash for Windows from Docker Tools and run the shell script from Git Bash terminal(I prefer Git Bash).Download and Copy Oracle18xe rpm file to 18.40 folder

This is output from Git Bash for windows

 xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 (master)
    $ ls -lrt
    total 2513906
    -rw-r--r-- 1 user 197121         74 Dec 28 13:17 Checksum.xe
    -rw-r--r-- 1 user 197121       2675 Dec 28 13:17 Dockerfile.xe
    -rwxr-xr-x 1 user 197121       1137 Dec 28 13:17 checkDBStatus.sh*
    -rwxr-xr-x 1 user 197121        905 Dec 28 13:17 checkSpace.sh*
    -rw-r--r-- 1 user 197121        533 Dec 28 13:17 oracle-xe-18c.conf
    -rwxr-xr-x 1 user 197121       6132 Dec 28 13:17 runOracle.sh*
    -rwxr-xr-x 1 user 197121        775 Dec 28 13:17 setPassword.sh*
    -rw-r--r-- 1 user 197121 2574155124 Dec 28 13:48 oracle-database-xe-18c-1.0-1.x86_64.rpm

After running shell script successfully

xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 (master)
$ docker image ls
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
oracle/database     18.4.0-xe           19b38be42ae6        30 minutes ago      8.44GB
oraclelinux         7-slim              0d49b70bc432        9 days ago          118MB

xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles (master)
$ docker run --name myxedb \
>     -d \
>     -p 51521:1521 \
>     -p 55500:5500 \
>     -e ORACLE_PWD=xxxxxxx \
>     -e ORACLE_CHARACTERSET=AL32UTF8 \
>     oracle/database:18.4.0-xe

xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 (master)
$ docker ps
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                             PORTS                                                        NAMES
afce013adb98        oracle/database:18.4.0-xe   "/bin/sh -c 'exec $O…"   19 seconds ago      Up 16 seconds (health: starting)   8080/tcp, 0.0.0.0:51521->1521/tcp, 0.0.0.0:55500->5500/tcp   myxedb

You can connect to database via sqlplus or login into docker container using Windows Dos

    D:\>docker exec -it --user=oracle myxedb bash

[oracle@afce013adb98 /]$ . oraenv
ORACLE_SID = [XE] ?
The Oracle base remains unchanged with value /opt/oracle

[oracle@afce013adb98 /]$ sqlplus / as sysdba

    SQL*Plus: Release 18.0.0.0.0 - Production on Sun Dec 29 05:04:32 2019
    Version 18.4.0.0.0

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

    Connected to:
    Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
    Version 18.4.0.0.0

    SQL> select banner from v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

  P.S:-Make sure you logged into docker hub from git bash terminal
  docker login

To use WSL subsystem bash terminal add path and alias to .bashrc

export PATH="$HOME/bin:$HOME/.local/bin:/mnt/c/Program\Files/Docker/Docker/resources/bin:$PATH"
alias docker=docker.exe
alias docker-compose=docker-compose.exe

From WSL Bash terminal

xxxx@localhost:~# docker -v
Docker version 19.03.8, build afacb8b